﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Services;
using System.Xml;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Skyworth.CRM.Common;
using Skyworth.CRM.Service.IBLL;

namespace Skyworth.CRM.Web.Service
{
    /// <summary>
    /// Summary description for $codebehindclassname$
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    public class SalesStockHandler : IHttpHandler
    {
        private static ISalesStockBLL letterofAdviceBLL = IocContainer.Instance.Resolve<ISalesStockBLL>();
        public void ProcessRequest(HttpContext context)
        {
            string customer = context.Request["customer"];
            string empName = context.Request["empName"];
            string code = context.Request["code"];
            string startDate = context.Request["startDate"];
            string endDate = context.Request["endDate"];
            string sidx = context.Request["sidx"];
            string sord = context.Request["sord"];

            StringBuilder strWhere = GetWhereString(customer, empName, code, startDate, endDate);
             
            MemoryStream filestream = new MemoryStream();

            using (FileStream file = new FileStream(context.Server.MapPath("~/App_Data/SalesStockTemplate.xls"), FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                ISheet sheet1 = hssfworkbook.GetSheet("销售出库单");
                IRow row1 = sheet1.GetRow(1);
                row1.GetCell(2).SetCellValue(code);
                row1.GetCell(5).SetCellValue(startDate);
                row1.GetCell(8).SetCellValue(endDate);
                IRow row2 = sheet1.GetRow(2);
                row2.GetCell(2).SetCellValue(customer);
                row2.GetCell(5).SetCellValue(empName);

                IRow row6 = sheet1.GetRow(6);
                IRow row8 = sheet1.GetRow(8);
                IRow row9 = sheet1.GetRow(9);
                int rowNo = 10;
                int i = 1;

                string xmlData = letterofAdviceBLL.Export2Excel(strWhere.ToString());
                if (!string.IsNullOrEmpty(xmlData))
                {
                    XmlDocument xmlDoc = new XmlDocument();
                    xmlDoc.LoadXml(xmlData);
                    XmlNodeList nodeList = xmlDoc.SelectNodes("//Root/a");
                    foreach (XmlNode xmlNode in nodeList)
                    {
                        BuildNewRow(sheet1, rowNo, row6, i, xmlNode);

                        rowNo = BuildChildTable(sheet1, rowNo, xmlNode, row8, row9);
                        rowNo++;
                        i++;
                    }
                }
                row6.Height = 0;
                sheet1.GetRow(7).Height = 0;
                sheet1.GetRow(8).Height = 0;
                sheet1.GetRow(9).Height = 0;
                hssfworkbook.Write(filestream);
            }



            string filename = DateTime.Now.ToString("yyyyMMdd") + "导出-销售出库单.xls";

            context.Response.ContentType = "application/vnd.ms-excel";

            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.Charset = "";
            context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            context.Response.BinaryWrite(filestream.GetBuffer());
            context.Response.End();
        }

        private void BuildNewRow(ISheet sheet1, int rowNo, IRow row6, int i, XmlNode xmlNode)
        {
            if (xmlNode.Attributes != null)
            {
                IRow row = sheet1.CreateRow(rowNo);
                row.Height = 400;

                ICell cell = row.CreateCell(1);
                cell.CellStyle = row6.GetCell(1).CellStyle;
                cell.SetCellValue(i);
                cell = row.CreateCell(2);
                cell.CellStyle = row6.GetCell(2).CellStyle;
                cell.SetCellValue(xmlNode.Attributes["Fnumber"] != null ? xmlNode.Attributes["Fnumber"].Value : "");
                cell = row.CreateCell(3);
                cell.CellStyle = row6.GetCell(3).CellStyle;
                cell.SetCellValue(xmlNode.Attributes["Fdate"] != null ? xmlNode.Attributes["Fdate"].Value : "");
                cell = row.CreateCell(4);
                cell.CellStyle = row6.GetCell(4).CellStyle;
                cell.SetCellValue(xmlNode.Attributes["FCustid"] != null ? xmlNode.Attributes["FCustid"].Value : "");
                cell = row.CreateCell(5);
                cell.CellStyle = row6.GetCell(5).CellStyle;
                cell.SetCellValue(xmlNode.Attributes["FCyID"] != null ? xmlNode.Attributes["FCyID"].Value : "");
                cell = row.CreateCell(6);
                cell.CellStyle = row6.GetCell(6).CellStyle;
                cell.SetCellValue(xmlNode.Attributes["FEmpName"] != null ? xmlNode.Attributes["FEmpName"].Value : "");
            }
        }

        private int BuildChildTable(ISheet sheet1, int rowNo, XmlNode xmlNode, IRow row8, IRow row9)
        {
            XmlNodeList childNodes = xmlNode.SelectNodes("b[@FSaleStockID]");
            if (childNodes != null && childNodes.Count > 0)
            {
                BuildChildTableHeader(sheet1, rowNo, row8);
                rowNo = BuildChildRow(sheet1, rowNo, childNodes, row9);
                rowNo++;
            }
            return rowNo;
        }

        private void BuildChildTableHeader(ISheet sheet1, int rowNo, IRow row8)
        {
            IRow rowChildHeader = sheet1.CreateRow(rowNo + 1);
            rowChildHeader.Height = 420;
            for (int j = 2; j <= 11; j++)
            {
                ICell cellChildHeader = rowChildHeader.CreateCell(j);
                cellChildHeader.CellStyle = row8.GetCell(j).CellStyle;
                cellChildHeader.SetCellValue(row8.GetCell(j).StringCellValue);
            }
        }

        private int BuildChildRow(ISheet sheet1, int rowNo, XmlNodeList childNodes, IRow row9)
        {
            int n = 1;
            foreach (XmlNode childNode in childNodes)
            {
                if (childNode.Attributes != null)
                {
                    IRow rowChild = sheet1.CreateRow(rowNo + 2);
                    rowChild.Height = 350;
                    ICell cellChild = rowChild.CreateCell(2);
                    cellChild.CellStyle = row9.GetCell(2).CellStyle;
                    cellChild.SetCellValue(n);
                    cellChild = rowChild.CreateCell(3);
                    cellChild.CellStyle = row9.GetCell(3).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FOrderID"] != null
                                               ? childNode.Attributes["FOrderID"].Value
                                               : "");
                    cellChild = rowChild.CreateCell(4);
                    cellChild.CellStyle = row9.GetCell(4).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FOrdernumber"] != null
                                               ? childNode.Attributes["FOrdernumber"].Value
                                               : "");
                    cellChild = rowChild.CreateCell(5);
                    cellChild.CellStyle = row9.GetCell(5).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FDate"] != null
                                               ? childNode.Attributes["FDate"].Value
                                               : "");
                    cellChild = rowChild.CreateCell(6);
                    cellChild.CellStyle = row9.GetCell(6).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FProductname"] != null
                                               ? childNode.Attributes["FProductname"].Value
                                               : "");
                    cellChild = rowChild.CreateCell(7);
                    cellChild.CellStyle = row9.GetCell(7).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FProductnumber"] != null
                                               ? childNode.Attributes["FProductnumber"].Value
                                               : "");
                    cellChild = rowChild.CreateCell(8);
                    cellChild.CellStyle = row9.GetCell(8).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FQTy"] != null
                                               ? Convert.ToDouble(childNode.Attributes["FQTy"].Value)
                                               : 0);
                    cellChild = rowChild.CreateCell(9);
                    cellChild.CellStyle = row9.GetCell(9).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FPrice"] != null
                                               ? Convert.ToDouble(childNode.Attributes["FPrice"].Value)
                                               : 0);
                    cellChild = rowChild.CreateCell(10);
                    cellChild.CellStyle = row9.GetCell(10).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FAmount"] != null
                                               ? Convert.ToDouble(childNode.Attributes["FAmount"].Value)
                                               : 0);
                    cellChild = rowChild.CreateCell(11);
                    cellChild.CellStyle = row9.GetCell(11).CellStyle;
                    cellChild.SetCellValue(childNode.Attributes["FisR"] != null
                                               ? childNode.Attributes["FisR"].Value
                                               : "");
                    n++;
                    rowNo++;
                }
            }
            return rowNo;
        }

        private StringBuilder GetWhereString(string customer,string empName, string code, string startDate, string endDate)
        {
            StringBuilder strWhere = new StringBuilder(" where 1=1 ");
            if (!string.IsNullOrEmpty(customer))//客户
            {
                strWhere.Append(" and FCustid like '" + customer + "%'");
            }
            if (!string.IsNullOrEmpty(empName))
            {
                strWhere.Append(" and FEmpName like '" + empName + "%'");
            }
            if (!string.IsNullOrEmpty(code))
            {
                strWhere.Append(" and Fnumber= '" + code + "'");
            }
            if (!string.IsNullOrEmpty(startDate) && string.IsNullOrEmpty(endDate))
            {
                strWhere.Append(" and Fdate >='" + startDate + "'");
            }
            else if (string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
            {
                strWhere.Append(" and Fdate <='" + endDate.Trim() + " 23:59:59'");
            }
            else if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
            {
                strWhere.Append(" and Fdate between '" + startDate + "' and '" + endDate.Trim() + " 23:59:59'");
            }
            return strWhere;
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}
